package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.P2pDqscDao;

import net.sf.json.JSONObject;

@Repository
public class P2pDqscDaoImpl extends P2PJdbcDao implements P2pDqscDao {

	private Logger logger = Logger.getLogger(P2pDqscDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryProListAll(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String proName = params.optString("proName");
		String proCode = params.optString("proCode");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select ID,PRODUCT_NAME,PRODUCT_CODE,PRODUCT_DJ_JE,PRODUCT_ZL,PRODUCT_KC_NUM,PRODUCT_KY_NUM,PRODUCT_SORT_NUM,date_format(PRODUCT_LR_DATE,'%Y-%m-%d %H:%i:%s') PRODUCT_LR_DATE,PRODUCT_FJ_PIC_NUM,PRODUCT_ZS_PIC,PRODUCT_XQ_PIC from t_zhfw_shopping_product where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(PRODUCT_LR_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(PRODUCT_LR_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(proName)) {
			sql.append(" and PRODUCT_NAME like :proName");
			paramMap.put("proName", "%" + proName + "%");
		}
		if (StringUtils.isNotEmpty(proCode)) {
			sql.append(" and PRODUCT_CODE like :proCode");
			paramMap.put("proCode", "%" + proCode + "%");
		}
		sql.append(" order by PRODUCT_LR_DATE limit :start,:limit");
		logger.info("queryProListAll sql is " + sql.toString());
		logger.info("queryProListAll paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String proName = params.optString("proName");
		String proCode = params.optString("proCode");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_shopping_product where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(PRODUCT_LR_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(PRODUCT_LR_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(proName)) {
			sql.append(" and PRODUCT_NAME like :proName");
			paramMap.put("proName", "%" + proName + "%");
		}
		if (StringUtils.isNotEmpty(proCode)) {
			sql.append(" and PRODUCT_CODE like :proCode");
			paramMap.put("proCode", "%" + proCode + "%");
		}
		logger.info("getCount sql is " + sql.toString());
		logger.info("getCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int insertPro(Map<String, Object> paramMap) {
		String sql = "insert into t_zhfw_shopping_product(PRODUCT_NAME,PRODUCT_CODE,PRODUCT_DJ_JE,PRODUCT_ZL,PRODUCT_KC_NUM,PRODUCT_KY_NUM,PRODUCT_SORT_NUM,PRODUCT_LR_DATE,PRODUCT_FJ_PIC_NUM,PRODUCT_ZS_PIC,PRODUCT_XQ_PIC) values(:spmc,:spbh,:djje,:spgg,:kcsl,:kysl,:px,now(),:picsl,:pich,:pic)";
		logger.info("insertPro sql is " + sql);
		logger.info("insertPro paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updatePro(Map<String, Object> paramMap) {
		String sql = "update t_zhfw_shopping_product set PRODUCT_NAME=:spmc,PRODUCT_CODE=:spbh,PRODUCT_DJ_JE=:djje,PRODUCT_ZL=:spgg,PRODUCT_KC_NUM=:kcsl,PRODUCT_KY_NUM=:kysl,PRODUCT_SORT_NUM=:px,PRODUCT_FJ_PIC_NUM=:picsl,PRODUCT_ZS_PIC=:pich,PRODUCT_XQ_PIC=:pic where ID=:id";
		logger.info("updatePro sql is " + sql);
		logger.info("updatePro paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> queryShdzListAll(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String yhm = params.optString("yhm");
		String shrxm = params.optString("shrxm");
		String sjhm = params.optString("sjhm");
		String sjhmBy = params.optString("sjhmBy");
		String shdz = params.optString("shdz");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select ID, SHR_NAME, SHR_SJHM, SHR_SJHM2, SHR_SZDQ, SHR_ADDRESS, SHR_YB, USER_NAME, IS_MRDZ, SHR_STATUS from t_zhfw_shopping_sh where 1=1");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(shrxm)) {
			sql.append(" and SHR_NAME like :shrxm");
			paramMap.put("shrxm", "%" + shrxm + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and SHR_SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(sjhmBy)) {
			sql.append(" and SHR_SJHM2 like :sjhmBy");
			paramMap.put("sjhmBy", "%" + sjhmBy + "%");
		}
		if (StringUtils.isNotEmpty(shdz)) {
			sql.append(" and SHR_SZDQ like :shdz or SHR_ADDRESS like :shdz");
			paramMap.put("shdz", "%" + shdz + "%");
		}
		sql.append(" order by USER_NAME limit :start,:limit");
		logger.info("queryShdzListAll sql is " + sql.toString());
		logger.info("queryShdzListAll paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getShdzListCount(JSONObject params) {
		String yhm = params.optString("yhm");
		String shrxm = params.optString("shrxm");
		String sjhm = params.optString("sjhm");
		String sjhmBy = params.optString("sjhmBy");
		String shdz = params.optString("shdz");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_shopping_sh where 1=1");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(shrxm)) {
			sql.append(" and SHR_NAME like :shrxm");
			paramMap.put("shrxm", "%" + shrxm + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and SHR_SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(sjhmBy)) {
			sql.append(" and SHR_SJHM2 like :sjhmBy");
			paramMap.put("sjhmBy", "%" + sjhmBy + "%");
		}
		if (StringUtils.isNotEmpty(shdz)) {
			sql.append(" and SHR_SZDQ like :shdz or SHR_ADDRESS like :shdz");
			paramMap.put("shdz", "%" + shdz + "%");
		}
		logger.info("getShdzListCount sql is " + sql.toString());
		logger.info("getShdzListCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryGwcListAll(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String gwczt = params.optString("gwczt");
		String spmc = params.optString("spmc");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select t1.ID,t1.USER_NAME,t1.PRODUCT_NUM,t1.GWC_PRODUCT_STATE,date_format(t1.CREATE_DATE,'%Y-%m-%d') CREATE_DATE,t2.PRODUCT_NAME,t2.PRODUCT_DJ_JE,t2.PRODUCT_ZL");
		sql.append(
				" from t_zhfw_shopping_gwc t1 left join t_zhfw_shopping_product t2 on t1.PRODUCT_ID=t2.id where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and t1.USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(gwczt)) {
			sql.append(" and t1.GWC_PRODUCT_STATE =:gwczt");
			paramMap.put("gwczt", gwczt);
		}
		if (StringUtils.isNotEmpty(spmc)) {
			sql.append(" and t2.PRODUCT_NAME like :spmc");
			paramMap.put("spmc", spmc);
		}
		sql.append(" order by t1.CREATE_DATE limit :start,:limit");
		logger.info("queryGwcListAll sql is " + sql.toString());
		logger.info("queryGwcListAll paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getGwcCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String gwczt = params.optString("gwczt");
		String spmc = params.optString("spmc");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*)");
		sql.append(
				" from t_zhfw_shopping_gwc t1 left join t_zhfw_shopping_product t2 on t1.PRODUCT_ID=t2.id where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and t1.USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(gwczt)) {
			sql.append(" and t1.GWC_PRODUCT_STATE =:gwczt");
			paramMap.put("gwczt", gwczt);
		}
		if (StringUtils.isNotEmpty(spmc)) {
			sql.append(" and t2.PRODUCT_NAME like :spmc");
			paramMap.put("spmc", spmc);
		}
		logger.info("getGwcCount sql is " + sql.toString());
		logger.info("getGwcCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryOrderListAll(JSONObject params) {
		logger.info(params);
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String fkksDate = params.optString("fkksDate");
		String fkjsDate = params.optString("fkjsDate");
		String yhm = params.optString("yhm");
		String ddh = params.optString("ddh");
		String ddzt = params.optString("ddzt");
		String psfs = params.optString("psfs");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select temp4.*,tt4.GYH OA_GYH,tt4.DQ,date_format(TT5.CHANGE_DATE,'%Y-%m-%d %H:%i:%s') FKSJ");
		sql.append(" from (select temp3.* ");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end");
		sql.append(" else temp3.tjr2 end");
		sql.append(" else temp3.tjr1 end TJR ");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else '03' end");
		sql.append(" else '02' end");
		sql.append(" else '01' end TJDJ");
		sql.append(" from (select temp2.* ");
		sql.append(" ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(" (select tu3.tjr from t_zhfw_user_tgcode tg3");
		sql.append(" inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(" inner join t_zhfw_user tu3 on tg1.USER_NAME=tu3.USER_NAME");
		sql.append("  where tg3.USER_NAME=temp2.USER_NAME) end tjr3 ");
		sql.append(
				" from (SELECT t1.ID,t1.ORDER_BH,t1.EXPRESS_BH,t1.EXPRESS_COM_NAME,t1.EXPRESS_JE,t1.REAL_JE,t1.REF_SH_ID,t1.ORDER_STATE,t1.MESSAGE,t1.USER_NAME,date_format(t1.CREATE_DATE,'%Y-%m-%d %H:%i:%s') CREATE_DATE,t1.ZF_TYPE,t1.QSR_USER_NAME,date_format(t1.QS_DATE,'%Y-%m-%d %H:%i:%s') QS_DATE,t2.SHR_NAME,t2.SHR_SJHM,t2.SHR_SJHM2,t2.SHR_SZDQ,t2.SHR_ADDRESS,tt1.tjr tjr1");
		sql.append(" ,case when tt1.tjr is null or tt1.tjr = '' then");
		sql.append(" (select tu2.tjr from t_zhfw_user_tgcode tg2");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME");
		sql.append(" where tg2.USER_NAME=t1.USER_NAME) end tjr2");
		sql.append(
				" FROM  t_zhfw_shopping_order t1 LEFT JOIN t_zhfw_user tt1 on t1.USER_NAME = tt1.USER_NAME and tt1.DISABLED=1 ");
		sql.append(" LEFT JOIN t_zhfw_shopping_sh t2 on t1.REF_SH_ID = t2.ID where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.CREATE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and t1.USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(ddh)) {
			sql.append(" and t1.ORDER_BH like :ddh");
			paramMap.put("ddh", "%" + ddh + "%");
		}
		if (StringUtils.isNotEmpty(ddzt)) {
			sql.append(" and t1.ORDER_STATE =:ddzt");
			paramMap.put("ddzt", ddzt);
		}
		if (StringUtils.isNotEmpty(psfs)) {
			sql.append(" and t1.ZF_TYPE =:psfs");
			paramMap.put("psfs", psfs);
		}
		sql.append(" )temp2) temp3) temp4 ");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR");
		sql.append(" left join t_zhfw_dq_details_log TT5 on TT5.OCCUR_CHANGE_ID=temp4.ID");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(fkksDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')>=:fkksDate");
			paramMap.put("fkksDate", fkksDate);
		}
		if (StringUtils.isNotEmpty(fkjsDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')<=:fkjsDate");
			paramMap.put("fkjsDate", fkjsDate);
		}
		sql.append(" order by temp4.CREATE_DATE desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryOrderListAll sql is " + sql.toString());
		logger.info("queryOrderListAll paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getOrderCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String fkksDate = params.optString("fkksDate");
		String fkjsDate = params.optString("fkjsDate");
		String yhm = params.optString("yhm");
		String ddh = params.optString("ddh");
		String ddzt = params.optString("ddzt");
		String psfs = params.optString("psfs");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_shopping_order temp4");
		sql.append(" left join t_zhfw_dq_details_log TT5 on TT5.OCCUR_CHANGE_ID=temp4.ID  where 1=1");

		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(temp4.CREATE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(temp4.CREATE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and temp4.USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(ddh)) {
			sql.append(" and temp4.ORDER_BH like :ddh");
			paramMap.put("ddh", "%" + ddh + "%");
		}
		if (StringUtils.isNotEmpty(ddzt)) {
			sql.append(" and temp4.ORDER_STATE =:ddzt");
			paramMap.put("ddzt", ddzt);
		}
		if (StringUtils.isNotEmpty(psfs)) {
			sql.append(" and temp4.ZF_TYPE =:psfs");
			paramMap.put("psfs", psfs);
		}
		if (StringUtils.isNotEmpty(fkksDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')>=:fkksDate");
			paramMap.put("fkksDate", fkksDate);
		}
		if (StringUtils.isNotEmpty(fkjsDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')<=:fkjsDate");
			paramMap.put("fkjsDate", fkjsDate);
		}
		logger.info("getOrderCount sql is " + sql.toString());
		logger.info("getOrderCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public String getSumOrderJe(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String fkksDate = params.optString("fkksDate");
		String fkjsDate = params.optString("fkjsDate");
		String yhm = params.optString("yhm");
		String ddh = params.optString("ddh");
		String ddzt = params.optString("ddzt");
		String psfs = params.optString("psfs");
		Map<String, Object> paramMap = new HashMap<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select round(sum(temp4.REAL_JE),0) HJJE from t_zhfw_shopping_order temp4");
		sql.append(" left join t_zhfw_dq_details_log TT5 on TT5.OCCUR_CHANGE_ID=temp4.ID  where 1=1");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(temp4.CREATE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(temp4.CREATE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and temp4.USER_NAME like :yhm");
			paramMap.put("yhm", "%" + yhm + "%");
		}
		if (StringUtils.isNotEmpty(ddh)) {
			sql.append(" and temp4.ORDER_BH like :ddh");
			paramMap.put("ddh", "%" + ddh + "%");
		}
		if (StringUtils.isNotEmpty(ddzt)) {
			sql.append(" and temp4.ORDER_STATE =:ddzt");
			paramMap.put("ddzt", ddzt);
		}
		if (StringUtils.isNotEmpty(psfs)) {
			sql.append(" and temp4.ZF_TYPE =:psfs");
			paramMap.put("psfs", psfs);
		}
		if (StringUtils.isNotEmpty(fkksDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')>=:fkksDate");
			paramMap.put("fkksDate", fkksDate);
		}
		if (StringUtils.isNotEmpty(fkjsDate)) {
			sql.append(" and date_format(TT5.CHANGE_DATE,'%Y-%m-%d')<=:fkjsDate");
			paramMap.put("fkjsDate", fkjsDate);
		}
		logger.info("getSumOrderJe sql is " + sql.toString());
		logger.info("getSumOrderJe paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<String>() {
			@Override
			public String extractData(ResultSet res) throws SQLException, DataAccessException {
				String hjje = "0";
				if (res.next()) {
					hjje = res.getString("HJJE");
				}
				return hjje;
			}
		});
	}

	@Override
	public List<Map<String, Object>> queryOrderProList(String orderbh) {
		String sql = "select t1.*,t2.PRODUCT_ZL from t_zhfw_shopping_order_product t1 left join t_zhfw_shopping_product t2 on t1.PRODUCT_ID=t2.ID where t1.ORDER_BH='"
				+ orderbh + "'";
		Map<String, Object> paramMap = new HashMap<>();
		return this.getNamedParameterJdbcTemplate().queryForList(sql, paramMap);
	}

	@Override
	public int getOrderProCount(String orderbh) {
		String sql = "select count(*) from t_zhfw_shopping_order_product where ORDER_BH='" + orderbh + "'";
		Map<String, Object> paramMap = new HashMap<>();
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}

	@Override
	public int updateOrderKdxx(JSONObject params) {
		String orderbh = params.optString("orderbh");
		String kdgs = params.optString("kdgs");
		String kdbh = params.optString("kdbh");
		String sql = "update t_zhfw_shopping_order set EXPRESS_COM_NAME=:kdgs,EXPRESS_BH=:kdbh where ORDER_BH=:orderbh";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("orderbh", orderbh);
		paramMap.put("kdgs", kdgs);
		paramMap.put("kdbh", kdbh);
		logger.info("updateOrderKdxx sql is " + sql);
		logger.info("updateOrderKdxx paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateOrderOver(JSONObject params) {
		String orderbh = params.optString("orderbh");
		String qsr = params.optString("qsr");
		String sql = "update t_zhfw_shopping_order set QSR_USER_NAME=:qsr,QS_DATE=now(),ORDER_STATE='02' where ORDER_BH=:orderbh";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("orderbh", orderbh);
		paramMap.put("qsr", qsr);
		logger.info("updateOrderOver sql is " + sql);
		logger.info("updateOrderOver paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> queryCheckDqList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String yhm=params.optString("yhm");
		String hdcz=params.optString("hdcz");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select * from (select t2.gyh GYH,ifnull(t2.DQ_JE,0) ZHSYDQE");
		sql.append(",sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end) XFDQE");
		sql.append(",sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end) HDDQE");
		sql.append(",(sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end)-sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end)-ifnull(t2.DQ_JE,0)) HDCZ");
		sql.append(" from t_zhfw_jbxx t2");
		sql.append(" left join ");
		sql.append("(select CHANGE_TYPE,ifnull(sum(OCCUR_JE),0) JE,USER_NAME from t_zhfw_dq_details_log group by CHANGE_TYPE,USER_NAME) t1 on t1.USER_NAME=t2.GYH group by t2.GYH) T");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and T.GYH like :yhm");
			paramMap.put("yhm", yhm+"%");
		}
		if (StringUtils.isNotEmpty(hdcz)&&"00".equals(hdcz)) {
			sql.append(" and T.HDCZ = 0");
		}else if (StringUtils.isNotEmpty(hdcz)&&"01".equals(hdcz)) {
			sql.append(" and T.HDCZ <> 0");
		}
		sql.append(" order by T.HDCZ DESC,T.HDDQE DESC,T.XFDQE DESC,T.ZHSYDQE DESC");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryCheckDqList sql is " + sql.toString());
		logger.info("queryCheckDqList paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getCheckDqCount(JSONObject params) {
		String yhm=params.optString("yhm");
		String hdcz=params.optString("hdcz");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from (select t2.gyh GYH,ifnull(t2.DQ_JE,0) ZHSYDQE");
		sql.append(",sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end) XFDQE");
		sql.append(",sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end) HDDQE");
		sql.append(",(sum(case when CHANGE_TYPE in('1001','1002') then t1.JE else 0 end)-sum(case when CHANGE_TYPE='1003' then t1.JE else 0 end)-ifnull(t2.DQ_JE,0)) HDCZ");
		sql.append(" from t_zhfw_jbxx t2");
		sql.append(" left join ");
		sql.append("(select CHANGE_TYPE,ifnull(sum(OCCUR_JE),0) JE,USER_NAME from t_zhfw_dq_details_log group by CHANGE_TYPE,USER_NAME) t1 on t1.USER_NAME=t2.GYH group by t2.GYH) T");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and T.GYH=:yhm");
			paramMap.put("yhm", yhm);
		}
		if (StringUtils.isNotEmpty(hdcz)&&"00".equals(hdcz)) {
			sql.append(" and T.HDCZ = 0");
		}else if (StringUtils.isNotEmpty(hdcz)&&"01".equals(hdcz)) {
			sql.append(" and T.HDCZ <> 0");
		}
		logger.info("getCheckDqCount sql is " + sql.toString());
		logger.info("getCheckDqCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

}
